﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Manager.Camada.Logica;
using Manager.Camada.Negocio;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace Manager.Camada.BancoDados
{
    public class ContatoDAOImpl : ContatoDAO
    {
        string strConnection = ConfigurationManager.ConnectionStrings["ConexaoBanco"].ConnectionString.ToString();

        public void inserirContato(ContatoVO contato)
        {
            using (SqlConnection sqlconnection = new SqlConnection(strConnection))
            {
                sqlconnection.Open();

                SqlTransaction transaction = sqlconnection.BeginTransaction("SampleTransaction");

                try
                {
                    string sqlInsert = "INSERT INTO CONTATO " +
                                      "(IDCLIENTE,DEPARTAMENTO,CONTATO,EMAIL,TELEFONE,CELULAR) " +
                                      "VALUES " +
                                      "(@IdCliente,@Departamento,@Contato,@Email,@Telefone,@Celular)";//SELECT SCOPE_IDENTITY()";

                    SqlCommand cmd = new SqlCommand(sqlInsert, sqlconnection, transaction);

                    cmd.Parameters.Add(new SqlParameter("@IdCliente", contato.idCliente));
                    cmd.Parameters.Add(new SqlParameter("@Departamento", contato.departamento));
                    cmd.Parameters.Add(new SqlParameter("@Contato", contato.contato));
                    cmd.Parameters.Add(new SqlParameter("@Email", contato.email));
                    cmd.Parameters.Add(new SqlParameter("@Telefone", contato.telefone));
                    cmd.Parameters.Add(new SqlParameter("@Celular", contato.celular));

                    cmd.ExecuteScalar();

                    transaction.Commit();
                    sqlconnection.Close();

                }
                catch
                {
                    transaction.Rollback();
                    sqlconnection.Close();
                }
            }
        }

        public List<ContatoVO> buscarContatosEmpresa(int idEmpresa)
        {
            List<ContatoVO> listaContato = new List<ContatoVO>();

            using (SqlConnection sqlconnection = new SqlConnection(strConnection))
            {
                sqlconnection.Open();

                SqlTransaction transaction = sqlconnection.BeginTransaction("SampleTransaction");

                try
                {
                    string sqlBusca = "SELECT C.IDCONTATO,C.IDCLIENTE,C.DEPARTAMENTO,C.CONTATO,C.EMAIL,C.TELEFONE,C.CELULAR " +
                                      "FROM EMPRESA A " +
                                      "INNER JOIN CLIENTE B ON B.IDEMPRESA = A.IDEMPRESA " +
                                      "INNER JOIN CONTATO C ON C.IDCLIENTE = B.IDCLIENTE " +
                                      "WHERE B.IDEMPRESA = @IdEmpresa";

                    SqlCommand cmd = new SqlCommand(sqlBusca, sqlconnection, transaction);

                    cmd.Parameters.AddWithValue("@IdEmpresa", idEmpresa);

                    SqlDataReader dr = cmd.ExecuteReader();

                    DataTable tblContato = new DataTable();
                    tblContato.Load(dr);

                    if (tblContato.Rows.Count > 0)
                    {
                        listaContato = mountListaContato(tblContato);
                    }

                    transaction.Commit();
                    sqlconnection.Close();
                }
                catch
                {
                    transaction.Rollback();
                    sqlconnection.Close();
                }

                return listaContato;
            }
        }

        private List<ContatoVO> mountListaContato(DataTable tblContato)
        {
            List<ContatoVO> listaContato = new List<ContatoVO>();

            foreach (DataRow row in tblContato.Rows)
            {
                ContatoVO contato = new ContatoVO();

                contato.idContato = Convert.ToInt32(row["IDCONTATO"]);
                contato.idCliente = Convert.ToInt32(row["IDCLIENTE"]);
                contato.departamento = Convert.ToString(row["DEPARTAMENTO"]);
                contato.contato = Convert.ToString(row["CONTATO"]);
                contato.email = Convert.ToString(row["EMAIL"]);
                contato.telefone = Convert.ToInt32(row["TELEFONE"]);
                contato.celular = Convert.ToInt32(row["CELULAR"]);

                listaContato.Add(contato);
            }

            return listaContato;
        }

    }
}
